﻿using System;
using System.Collections.Generic;
using System.Text;
using OgilvyOne.IDAL;
using OgilvyOne.Model;
using System.Data;
using System.Data.SqlClient;

namespace OgilvyOne.SqlServerDAL
{
    public class ModelManage:ModelManageIDAL
    {
        string connstr = SQLHelper.CONN_STRING_NON_DTC;
        /// <summary>
        /// 列表查询（多条数据）
        /// </summary>
        /// <param name="upid"></param>
        /// <returns></returns>
        public System.Data.DataSet GetModel(string strModel)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select tem_id,tem_name ");
            strSql.Append(" from CMS_TEMPLET_INFO");
            if (strModel.Trim() != "")
            {
                strSql.Append(" where " + strModel);
            }
            strSql.Append(" order by tem_name desc");
            return SQLHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 添加一条数据
        /// </summary>
        /// <param name="upid"></param>
        /// <returns></returns>
        public int AddModel(ModelManageModel addModel)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into CMS_TEMPLET_INFO (item_id,tem_name,tem_content,isdel)");
            strSql.Append("VALUES (@item_id,@tem_name,@tem_content,@isdel)");
            SqlParameter[] parameters = {
                    new SqlParameter("@item_id",SqlDbType.VarChar,50),
                    new SqlParameter("@tem_name",SqlDbType.VarChar,255),
                    new SqlParameter("@tem_content",SqlDbType.NText),
                    new SqlParameter("@isdel",SqlDbType.VarChar,1),
                                        };
            parameters[0].Value = addModel.ItemID;
            parameters[1].Value = addModel.TemName;
            parameters[2].Value = addModel.TemCotent;
            parameters[3].Value = addModel.Isdel;

            int ts = 1;
            return ts = SQLHelper.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        /// <param name="upid"></param>
        /// <returns></returns>
        public bool deleteModel(int CID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from CMS_TEMPLET_INFO  ");
            strSql.Append(" where tem_id=@tid");
            SqlParameter[] parameters = {
					new SqlParameter("@tid", SqlDbType.Int,4)
             };
            parameters[0].Value = CID;
            return SQLHelper.ExecuteNonQuery(connstr, CommandType.Text, strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 查询单条数据
        /// </summary>
        /// <param name="upid"></param>
        /// <returns></returns>
        private const string COLUMN_SQL = "select tem_id,item_id,tem_name,tem_content,isdel from CMS_TEMPLET_INFO where tem_id=@tid";
        public ModelManageModel getModel(int tid)
        {
            ModelManageModel mmm = null;
            try
            {

                SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@tid", SqlDbType.Int)
                                                       };
                parms[0].Value = tid;
                using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, COLUMN_SQL, parms))
                {
                    if (rdr.Read())
                    {
                        mmm = new ModelManageModel();
                        mmm.TmenID = rdr.GetInt32(0);
                        mmm.ItemID = rdr.GetString(1);
                        mmm.TemName = rdr.GetString(2);
                        mmm.TemCotent = rdr.GetString(3);
                        mmm.Isdel = !rdr.IsDBNull(4) && rdr["isdel"] != null ? Convert.ToInt32(rdr["isdel"]) : 0;
                    }
                }

            }
            catch (Exception ex)
            {
                throw;
            }
            return mmm;
        }


        /// <summary>
        /// 更新一条数据
        /// </summary>
        /// <param name="upid"></param>
        /// <returns></returns>
        public int updateModel(ModelManageModel mmm)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE CMS_TEMPLET_INFO SET item_id=@itemid,tem_name=@temname,tem_content=@temcontent,isdel=@isdel");
            strSql.Append("  WHERE tem_id=@tid");
            SqlParameter[] parameters = {
                    new SqlParameter("@tid",SqlDbType.Int),
                    new SqlParameter("@itemid",SqlDbType.VarChar,50),
                    new SqlParameter("@temname",SqlDbType.VarChar,255),
                    new SqlParameter("@temcontent",SqlDbType.NText),
                    new SqlParameter("@isdel",SqlDbType.VarChar,1)
                    };
            parameters[0].Value = mmm.TmenID;
            parameters[1].Value = mmm.ItemID;
            parameters[2].Value = mmm.TemName;
            parameters[3].Value = mmm.TemCotent;
            parameters[4].Value = mmm.Isdel;

            int ts = 1;
            return ts = SQLHelper.ExecuteSql(strSql.ToString(), parameters);
        }

    }
}
